﻿Imports System.Data.SqlClient

Public Class frmCorporateSearchEngine

    Function GetColumnName() As String
        If Me.CombAmountColumn.SelectedItem = "Sum Insured" Then
            Return "SumInsur"

        ElseIf Me.CombAmountColumn.SelectedItem = "Total Premium" Then
            Return "TotalPremium"

        ElseIf Me.CombAmountColumn.SelectedItem = "Net Premium" Then
            Return "NetPremium"

        ElseIf Me.CombAmountColumn.SelectedItem = "L & A Comm." Then
            Return "CompComm"

        ElseIf Me.CombAmountColumn.SelectedItem = "Producer Comm." Then
            Return "ProducerComm"
        End If
    End Function

    Sub FillPolicies()
        Me.ErrorProvider1.Clear()

        Try
            Me.Cursor = Cursors.WaitCursor

            'Check filtering condition
            Dim StrCond, Column As String

            StrCond += "and ClientName Like N'%" & Me.txtClientName.Text & "%' "

            StrCond += "and InsurComp Like N'%" & Me.CombInsurComp.Text & "%' "

            StrCond += "and ClassName Like N'%" & Me.CombInsurClasses.Text & "%' "

            StrCond += "and PolicyNo Like N'%" & Me.txtPolicyNo.Text.Trim & "%' "

            If Me.txtEndorsNo.Text.Trim.Length <> 0 Then
                StrCond += "and EndorsNo Like N'%" & Me.txtEndorsNo.Text.Trim & "%' "
            End If

            If Me.CombEndorsType.Text.Trim.Length <> 0 Then
                StrCond += "and EndorsType Like N'%" & Me.CombEndorsType.Text.Trim & "%' "
            End If

            If Me.ChIssue.Checked = True Then
                StrCond += "and IssueDate>=N'" & Me.DTPIssueFrom.Value.ToShortDateString & " 00:00:00' " & _
                           "and IssueDate<=N'" & Me.DTPIssueTo.Value.ToShortDateString & " 23:23:59'"
            End If

            If Me.ChInception.Checked = True Then
                StrCond += "and PeriodFrom>=N'" & Me.DTPIncFrom.Value.ToShortDateString & " 00:00:00' " & _
                           "and PeriodFrom<=N'" & Me.DTPIncTo.Value.ToShortDateString & " 23:23:59'"
            End If

            If Me.ChAmount.Checked = True Then
                If Me.CombAmountColumn.SelectedIndex = -1 Then
                    Me.ErrorProvider1.SetError(Me.CombAmountColumn, "Please fill in all details")
                    Me.Cursor = Cursors.Default
                    Exit Sub

                ElseIf Me.CombOperator.SelectedIndex = -1 Then
                    Me.ErrorProvider1.SetError(Me.CombOperator, "Please fill in all details")
                    Me.Cursor = Cursors.Default
                    Exit Sub
                Else
                    Column = GetColumnName()
                    If Me.CombOperator.SelectedItem = "Between" Then
                        StrCond += "and " & Column & " Between " & Me.txtAmount.Value & " and " & Me.txtAmount2.Value
                    Else
                        StrCond += "and " & Column & Me.CombOperator.SelectedItem & Me.txtAmount.Value
                    End If
                End If
            End If

            Dim cmd As New SqlCommand("Select SNo,ClientNo,ClientName,InsurComp,ClassName,IsNull(AccNo,N'') AccNo,PolicyNo," & _
                                      "IsNull(EndorsNo,N'') EndorsNo,IsNull(EndorsType,N'Policy') EndorsType,SumInsur,TotalPremium,NetPremium," & _
                                      "CompComm,ProducerComm,IsNull(IssueDate,N'') IssueDate,PeriodFrom,PeriodTo,SavedUser,SavedDate,ApprovedUser,ApprovedDate,Department " & _
                                      "From Policies Where Done=1 " & StrCond, cnn)
            Dim Reader As SqlDataReader

            Me.GridPolicies.Rows.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While (Reader.Read)
                Me.GridPolicies.Rows.Add(New String() {Reader.Item("SNo"), Reader.Item("Department"), Reader.Item("ClientNo"), Reader.Item("ClientName"), _
                                                       Reader.Item("InsurComp"), Reader.Item("ClassName"), Reader.Item("AccNo"), Reader.Item("PolicyNo"), _
                                                       Reader.Item("EndorsNo"), Reader.Item("EndorsType"),
                                                       CDbl(Reader.Item("SumInsur")).ToString("N2"), _
                                                       CDbl(Reader.Item("TotalPremium")).ToString("N2"), _
                                                       CDbl(Reader.Item("NetPremium")).ToString("N2"), _
                                                       CDbl(Reader.Item("CompComm")).ToString("N2"), _
                                                       CDbl(Reader.Item("ProducerComm")).ToString("N2"), _
                                                       CDate(Reader.Item("IssueDate")).ToString("yyyy/MM/dd"), _
                                                       CDate(Reader.Item("PeriodFrom")).ToString("yyyy/MM/dd"), _
                                                       CDate(Reader.Item("PeriodTo")).ToString("yyyy/MM/dd"), _
                                                       Reader.Item("SavedUser"), _
                                                       CDate(Reader.Item("SavedDate")).ToString("yyyy/MM/dd"), _
                                                       Reader.Item("ApprovedUser"), _
                                                       CDate(Reader.Item("ApprovedDate")).ToString("yyyy/MM/dd"), "Edit", "Delete"})
            End While
            cnn.Close()

            'Count no. of results
            Me.lblResult.Text = "Results found: " & Me.GridPolicies.Rows.Count

            'Change font colour to red if Deletion
            For Each row As DataGridViewRow In Me.GridPolicies.Rows
                If CDbl(row.Cells(Me.DGColSumInsured.Index).Value) < 0 Then
                    row.Cells(Me.DGColType.Index).Style.ForeColor = Color.Red
                    row.Cells(Me.DGColSumInsured.Index).Style.ForeColor = Color.Red
                End If

                If CDbl(row.Cells(Me.DGColTotPrem.Index).Value) < 0 Then
                    row.Cells(Me.DGColTotPrem.Index).Style.ForeColor = Color.Red
                End If

                If CDbl(row.Cells(Me.DGColNetPrem.Index).Value) < 0 Then
                    row.Cells(Me.DGColNetPrem.Index).Style.ForeColor = Color.Red
                End If

                If CDbl(row.Cells(Me.DGColCompComm.Index).Value) < 0 Then
                    row.Cells(Me.DGColCompComm.Index).Style.ForeColor = Color.Red
                End If

                If CDbl(row.Cells(Me.DGColProdCOmm.Index).Value) < 0 Then
                    row.Cells(Me.DGColProdCOmm.Index).Style.ForeColor = Color.Red
                End If
            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub FillInsurCompanies()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim InsurCompanies As ArrayList = GetInsurCompaniesList()

            Me.CombInsurComp.Items.Clear()

            For Each InsurComp As String In InsurCompanies
                Me.CombInsurComp.Items.Add(InsurComp)
            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub FillInsurClasses()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim InsurClasses As ArrayList = GetInsurClassList()

            Me.CombInsurClasses.Items.Clear()

            For Each InsurComp As String In InsurClasses
                Me.CombInsurClasses.Items.Add(InsurComp)
            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub frmSearchEngine_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Me.WindowState = FormWindowState.Maximized

        FillInsurClasses()
        FillInsurCompanies()
    End Sub

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles btnSearch.Click
        FillPolicies()
    End Sub

    Private Sub ChIssue_CheckedChanged(sender As System.Object, e As System.EventArgs) Handles ChIssue.CheckedChanged
        If Me.ChIssue.Checked = True Then
            Me.DTPIssueFrom.Enabled = True
            Me.DTPIssueTo.Enabled = True
        Else
            Me.DTPIssueFrom.Enabled = False
            Me.DTPIssueTo.Enabled = False
        End If
    End Sub

    Private Sub ChInception_CheckedChanged(sender As System.Object, e As System.EventArgs) Handles ChInception.CheckedChanged
        If Me.ChInception.Checked = True Then
            Me.DTPIncFrom.Enabled = True
            Me.DTPIncTo.Enabled = True
        Else
            Me.DTPIncFrom.Enabled = False
            Me.DTPIncTo.Enabled = False
        End If
    End Sub

    Private Sub ChAmount_CheckedChanged(sender As System.Object, e As System.EventArgs) Handles ChAmount.CheckedChanged
        If Me.ChAmount.Checked = True Then
            Me.CombAmountColumn.Enabled = True
            Me.CombOperator.Enabled = True
            Me.txtAmount.Enabled = True
            Me.CombAmountColumn.SelectedIndex = -1
            Me.CombOperator.SelectedIndex = -1
            Me.txtAmount.Value = 0
        Else
            Me.CombAmountColumn.Enabled = False
            Me.CombOperator.Enabled = False
            Me.txtAmount.Enabled = False
            Me.CombAmountColumn.SelectedIndex = -1
            Me.CombOperator.SelectedIndex = -1
            Me.txtAmount.Value = 0
            Me.lblAnd.Visible = False
            Me.txtAmount2.Visible = False
            Me.txtAmount2.Value = 0
        End If
    End Sub

    Private Sub CombOperator_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles CombOperator.SelectedIndexChanged
        If Me.CombOperator.SelectedItem = "Between" Then
            Me.lblAnd.Visible = True
            Me.txtAmount2.Visible = True
            Me.txtAmount2.Value = 0
        Else
            Me.lblAnd.Visible = False
            Me.txtAmount2.Visible = False
            Me.txtAmount2.Value = 0
        End If
    End Sub
End Class